package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.MemVO;
import com.sds.shop.vo.ReplyVO;

public class ReplyDAO extends AbDao {
	private static ReplyDAO dao = new ReplyDAO();
	public static ReplyDAO getInstance(){
		return dao;
	}	

	public ArrayList<ReplyVO> getReplyList(String board_no){
		ArrayList<ReplyVO> list = new ArrayList<ReplyVO>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select * from tb_reply where board_no=? and del_flg=? order by reg_dtm desc";
			
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, board_no);
			ps.setBoolean(2, false);
			rs = ps.executeQuery();
						
			while(rs.next()){
				list.add(new ReplyVO(rs.getString(1), rs.getString(2), rs.getString(3),
						rs.getString(4), rs.getString(5), rs.getBoolean(6), rs.getString(7)));
				
			}
			
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps, rs);
		}
		
		return list;
	}
	
	public void registReply(String board_no, String reply, MemVO mem){
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select max(seq) from tb_reply where board_no=?";
			String seq = "1";
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, board_no);
			rs = ps.executeQuery();
			
			if(rs.next() && rs.getString(1) != null && !rs.getString(1).equals("")) seq = (Integer.parseInt(rs.getString(1)) + 1) + "";
			System.out.println(seq);
						
			sql = "insert into tb_reply values(?,?,?,?,?,?,sysdate)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, board_no);
			ps.setString(2, seq);
			ps.setString(3, mem.getMemId());
			ps.setString(4, mem.getMemNm());
			ps.setString(5, reply);
			ps.setBoolean(6, false);
			
			ps.executeUpdate();
			
			close(conn, ps, rs);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps, rs);
		}
	}
	
	public void deleteReply(String board_no, String seq){
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			String sql = "update tb_reply set del_flg=? where board_no=? and seq=?";
			conn = connect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, true);
			ps.setString(2, board_no);
			ps.setString(3, seq);
			ps.executeUpdate();
			
			close(conn, ps);
		}catch(Exception ex){
			System.out.println("getBoard exception : " + ex);
			close(conn, ps);
		}
	}
}
